﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data.SQLite;
using System.Linq;
using System.Reflection;

namespace Hervey
{
    public enum AlmType
    {
        Soft, Cylinder, Sensor, Axis, Other
    }
    public struct SqlLinkPara
    {
        /// <summary>
        /// 数据库服务器IP
        /// </summary>
        public string ServerAdd;
        /// <summary>
        /// 数据库登录用户名
        /// </summary>
        public string UserID;
        /// <summary>
        /// 数据库登录密码
        /// </summary>
        public string Password;
        /// <summary>
        /// 数据库名称
        /// </summary>
        public string DbName;
        ///// <summary>
        ///// 数据库文件绝对路径
        ///// </summary>
        //public string DbPath;
        /// <summary>
        /// 连接超时时间
        /// </summary>
        public int TimeOut;
    }

    public struct AccessLinkPara
    {
        /// <summary>
        /// 数据库文件绝对路径
        /// </summary>
        public string DbPath;
        /// <summary>
        /// 连接超时时间
        /// </summary>
        public int TimeOut;
    }

    public struct SqlLiteLinkPara
    {
        /// <summary>
        /// 数据库文件绝对路径
        /// </summary>
        public string DbPath;
        /// <summary>
        /// 连接超时时间
        /// </summary>
        public int TimeOut;
    }

    public struct DBRes
    {
        public bool bRtn;
        public string msg;
    }

    public interface IDatabase
    {
        bool Connect(out DBRes res);
        void DisConnect();
        DataTable ExcuteCmd_DataTable(string cmdText, out DBRes res);
        void ExcuteCmd(string cmdText, out DBRes res);
        void ExcuteProc(string procName, SqlParameter[] strParas, out DBRes res);
        //void ExcuteProc(string procName, out DBRes res);
        List<T> GetList<T>(string cmdText, out DBRes res);
        ObservableCollection<T> GetList2<T>(string cmdText, out DBRes res);
        void InsertRow<T>(string table, T _type, out DBRes res, bool ignoreID = true);
    }




    public class SQLHelper : IDatabase
    {
        SqlConnection DataBase_Connection = new SqlConnection();
        SqlCommand DataBase_Command = new SqlCommand();
        SqlDataAdapter DataBase_ConnectionAdapter = new SqlDataAdapter();

        object obj = new object();
        SqlLinkPara LinkPara;


        public SQLHelper(SqlLinkPara para)
        {
            LinkPara = para;
        }

        public bool Connect(out DBRes res)
        {
            res = new DBRes() { bRtn = true };
            if (DataBase_Connection.State == ConnectionState.Open) return true;
            try
            {
                string SubName = MethodBase.GetCurrentMethod().Name;
                DataBase_Connection.ConnectionString = "Data Source=" + LinkPara.ServerAdd + ";Initial Catalog=" + LinkPara.DbName + ";User ID=" + LinkPara.UserID + ";Password=" + LinkPara.Password + ";Connect Timeout=" + LinkPara.TimeOut;
                if (DataBase_Connection.State == ConnectionState.Closed)
                {
                    DataBase_Connection.Open();
                    res.bRtn = true;
                }
                else res.bRtn = false;
            }
            catch (Exception ex)
            {
                res.bRtn = false;
                res.msg = ex.ToString();
            }
            return res.bRtn;
        }

        public void DisConnect()
        {
            try
            {
                //if (DataBase_Connection.State != ConnectionState.Open)
                DataBase_Connection.Close();
                DataBase_Connection.Dispose();
            }
            catch (Exception ex)
            {
            }
        }

        public DataTable ExcuteCmd_DataTable(string CommandText, out DBRes res)
        {
            lock (obj)
            {
                res = new DBRes();
                if (!Connect(out res)) return null;
                try
                {
                    DataSet Res_DataSet = new DataSet();
                    Res_DataSet.Clear();
                    DataBase_Command.CommandText = CommandText;
                    DataBase_ConnectionAdapter = new SqlDataAdapter(DataBase_Command.CommandText, DataBase_Connection);
                    DataBase_ConnectionAdapter.Fill(Res_DataSet);
                    DisConnect();

                    res.bRtn = true;
                    if (Res_DataSet != null && Res_DataSet.Tables.Count > 0)
                    {
                        return Res_DataSet.Tables[0];
                    }
                    else
                    {
                        res.msg = "获取到的表为空!";
                        return null;
                    }
                }
                catch (Exception ex)
                {
                    res.bRtn = false;
                    res.msg = ex.Message;
                    return null;
                }
            }
        }

        public void ExcuteCmd(string commandText, out DBRes res)
        {
            lock (obj)
            {
                res = new DBRes();
                if (!Connect(out res)) return;
                try
                {
                    DataBase_Command = new SqlCommand(commandText, DataBase_Connection);
                    DataBase_Command.CommandType = CommandType.Text;
                    DataBase_Command.ExecuteNonQuery();
                    DisConnect();
                    res.bRtn = true;
                }
                catch (Exception ex)
                {
                    res.bRtn = false;
                    res.msg = ex.Message;
                }
                return;
            }
        }

        public void ExcuteProc(string procName, SqlParameter[] strParas, out DBRes res)
        {
            lock (obj)
            {
                res = new DBRes();
                if (!Connect(out res)) return;
                try
                {
                    DataBase_Command = new SqlCommand(procName, DataBase_Connection);
                    DataBase_Command.CommandType = CommandType.StoredProcedure;
                    if (strParas != null && strParas.Length > 0) DataBase_Command.Parameters.AddRange(strParas);
                    DataBase_Command.ExecuteNonQuery();
                    DisConnect();
                    res.bRtn = true;
                }
                catch (Exception ex)
                {
                    res.bRtn = false;
                    res.msg = ex.Message;
                }
            }
        }

        public List<T> GetList<T>(string commandText, out DBRes res)
        {
            lock (obj)
            {
                res = new DBRes();
                if (!Connect(out res)) return new List<T>();
                List<T> list = new List<T>();
                try
                {
                    DataTable dt = null;
                    dt = ExcuteCmd_DataTable(commandText, out res);
                    if ((dt == null) || (dt.Rows.Count <= 0))
                    { DisConnect(); return new List<T>(); }

                    Type type = typeof(T);
                    foreach (DataRow itemRow in dt.Rows)
                    {
                        T listitem = (T)Activator.CreateInstance(type);
                        Type t = listitem.GetType();
                        object val;
                        foreach (DataColumn itemCol in dt.Columns)
                        {
                            val = itemRow[itemCol];
                            val = val == null ? "" : val.ToString().Trim();
                            val = val == DBNull.Value ? "" : val.ToString().Trim();
                            PropertyInfo info = t.GetProperty(itemCol.ColumnName);
                            if (info == null || info.PropertyType == null)
                            {
                                continue;
                            }
                            string _t = info.PropertyType.FullName;
                            if (_t == typeof(double).ToString())
                            {
                                if (string.IsNullOrWhiteSpace(val.ToString())) val = 0;
                                info.SetValue(listitem, Convert.ChangeType(val, typeof(double)), null);
                            }
                            else if (_t == typeof(int).ToString())
                            {
                                if (string.IsNullOrWhiteSpace(val.ToString())) val = 0;
                                info.SetValue(listitem, Convert.ChangeType(val, typeof(int)), null);
                            }
                            else if (_t == typeof(DateTime).ToString())
                            {
                                if (val == null || string.IsNullOrWhiteSpace(val.ToString())) val = new DateTime(1900, 1, 1).ToString();
                                info.SetValue(listitem, Convert.ChangeType(val, typeof(DateTime)), null);
                            }
                            else if (_t == typeof(bool).ToString())
                            {
                                info.SetValue(listitem, Convert.ChangeType(val, typeof(bool)), null);
                            }
                            else
                            {
                                info.SetValue(listitem, val.ToString(), null);
                            }
                        }
                        list.Add(listitem);
                    }
                    res.bRtn = true;
                    return list;
                }
                catch (Exception ex)
                {
                    res.bRtn = false;
                    res.msg = ex.Message;
                }
                return list;
            }
        }

        public ObservableCollection<T> GetList2<T>(string commandText, out DBRes res)
        {
            res = new DBRes();
            List<T> li = GetList<T>(commandText, out res);
            return new ObservableCollection<T>(li);
        }

        public void InsertRow<T>(string table, T _type, out DBRes res, bool ignoreID = true)
        {
            try
            {
                res = new DBRes();
                Type t = typeof(T);
                PropertyInfo[] info = t.GetProperties();

                string colNames = "", vals = "";
                foreach (PropertyInfo item in info)
                {
                    if (item.Name == "ID" && ignoreID) continue;
                    colNames += item.Name + ",";
                    object val = item.GetValue(_type);
                    vals += $"'{val}',";
                }
                colNames = colNames.Substring(0, colNames.Length - 1);
                vals = vals.Substring(0, vals.Length - 1);
                string cmd = $"insert into {table}({colNames}) values({vals})";

                ExcuteCmd(cmd, out res);
            }
            catch (Exception ex)
            {
                res.bRtn = false;
                res.msg = ex.Message + "\r\n" + ex.StackTrace;
            }
        }

        #region 其它方法

        ///// <summary>
        ///// 执行一个无返回值的存储过程
        ///// </summary>
        ///// <param name="procName"></param>
        ///// <param name="strParas"></param>
        ///// <returns></returns>


        ///// <summary>
        ///// 执行一个无返回值的存储过程
        ///// </summary>
        ///// <param name="procName"></param>
        ///// <param name="strParas"></param>
        ///// <returns></returns>
        //public string ExcuteProc(string procName)
        //{
        //    lock (obj)
        //    {
        //        try
        //        {
        //            Connect();

        //            DataBase_Command = new SqlCommand(procName, DataBase_Connection);
        //            DataBase_Command.CommandType = CommandType.StoredProcedure;

        //            DataBase_Command.ExecuteNonQuery();
        //            DisConnect();
        //            return "OK";
        //        }
        //        catch (Exception ex)
        //        {
        //            return ex.ToString();
        //        }
        //    }
        //}

        /// <summary>
        /// 执行一个返回DataTable的存储过程
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="strParas"></param>
        /// <returns></returns>
        public string ExcuteProc(string procName, SqlParameter[] strParas, ref DataTable rtnDataTable)
        {
            lock (obj)
            {
                try
                {
                    DataSet Res_DataSet = new DataSet();
                    DataBase_Command = new SqlCommand(procName, DataBase_Connection);
                    DataBase_Command.CommandType = CommandType.StoredProcedure;

                    DataBase_Command.Parameters.AddRange(strParas);
                    DataBase_ConnectionAdapter = new SqlDataAdapter(DataBase_Command);
                    DataBase_ConnectionAdapter.Fill(Res_DataSet);
                    rtnDataTable = Res_DataSet.Tables[0];
                    return "OK";
                }
                catch (Exception ex)
                {
                    return ex.ToString();
                }
            }
        }

        ///// <summary>
        ///// 执行有一个返回值的存储过程
        ///// </summary>
        ///// <param name="procName"></param>
        ///// <param name="strParas"></param>
        ///// <returns></returns>
        //public string ExcuteProc(string procName, SqlParameter[] strParas,ref string rtn)
        //{
        //    lock (obj)
        //    {
        //        try
        //        {
        //            DataSet Res_DataSet = new DataSet();
        //            DataBase_Command = new SqlCommand(procName, DataBase_Connection);
        //            DataBase_Command.CommandType = CommandType.StoredProcedure;

        //            DataBase_Command.Parameters.AddRange(strParas);
        //            DataBase_ConnectionAdapter = new SqlDataAdapter(DataBase_Command);
        //            DataBase_ConnectionAdapter.Fill(Res_DataSet);
        //            rtn = Res_DataSet.Tables[0].Rows[0].ItemArray[0].ToString().Trim();
        //            return "OK";
        //        }
        //        catch (Exception ex)
        //        {
        //            rtn = "";
        //            return ex.ToString();
        //        }
        //    }
        //}

        ///// <summary>
        ///// 执行有一个返回值的存储过程
        ///// </summary>
        ///// <param name="procName"></param>
        ///// <param name="strParas"></param>
        ///// <returns></returns>
        //public string ExcuteProc(string procName, SqlParameter[] strParas, ref bool rtn)
        //{
        //    lock (obj)
        //    {
        //        try
        //        {
        //            DataBase_Command = new SqlCommand(procName, DataBase_Connection);
        //            DataBase_Command.CommandType = CommandType.StoredProcedure;

        //            DataBase_Command.Parameters.AddRange(strParas);
        //            SqlParameter para = new SqlParameter("@OutRes", SqlDbType.Int);
        //            para.Direction = ParameterDirection.Output;
        //            DataBase_Command.Parameters.Add(para);
        //            DataBase_Command.ExecuteNonQuery();//== 0 ? false : true;

        //            rtn = (int)DataBase_Command.Parameters["@OutRes"].Value > 0 ? true : false;
        //            return "OK";
        //        }
        //        catch (Exception ex)
        //        {
        //            rtn = false ;
        //            return ex.ToString();
        //        }
        //    }
        //}

        ///// <summary>
        ///// 执行有一个返回值的存储过程
        ///// </summary>
        ///// <param name="procName"></param>
        ///// <param name="strParas"></param>
        ///// <returns></returns>
        //public string ExcuteProc(string procName, SqlParameter[] strParas, ref int rtn)
        //{
        //    lock (obj)
        //    {
        //        try
        //        {
        //            DataBase_Command = new SqlCommand(procName, DataBase_Connection);
        //            DataBase_Command.CommandType = CommandType.StoredProcedure;

        //            DataBase_Command.Parameters.AddRange(strParas);
        //            SqlParameter para = new SqlParameter("@OutRes", SqlDbType.Int);
        //            para.Direction = ParameterDirection.Output;
        //            DataBase_Command.Parameters.Add(para);
        //            DataBase_Command.ExecuteNonQuery();//== 0 ? false : true;

        //            rtn = (int)DataBase_Command.Parameters["@OutRes"].Value;
        //            return "OK";
        //        }
        //        catch (Exception ex)
        //        {
        //            rtn = 0;
        //            return ex.ToString();
        //        }
        //    }
        //}

        ///// <summary>
        ///// 执行有2个返回值的存储过程
        ///// </summary>
        ///// <param name="procName"></param>
        ///// <param name="strParas"></param>
        ///// <param name="rtn">处理的条目个数</param>
        ///// <param name="str">返回信息</param>
        ///// <returns></returns>
        //public string ExcuteProc(string procName, SqlParameter[] strParas, ref int rtn, ref string str)
        //{
        //    lock (obj)
        //    {
        //        try
        //        {
        //            DataBase_Command = new SqlCommand(procName, DataBase_Connection);
        //            DataBase_Command.CommandType = CommandType.StoredProcedure;

        //            DataBase_Command.Parameters.AddRange(strParas);
        //            SqlParameter para = new SqlParameter("@OutRes", SqlDbType.Int);
        //            para.Direction = ParameterDirection.Output;
        //            DataBase_Command.Parameters.Add(para);
        //            SqlParameter para1 = new SqlParameter("@OutStr", SqlDbType.VarChar,200);
        //            para1.Direction = ParameterDirection.Output;
        //            DataBase_Command.Parameters.Add(para1);
        //            DataBase_Command.ExecuteNonQuery();//== 0 ? false : true;

        //            rtn = (int)DataBase_Command.Parameters["@OutRes"].Value;
        //            str = (string)DataBase_Command.Parameters["@OutStr"].Value;
        //            return "OK";
        //        }
        //        catch (Exception ex)
        //        {
        //            rtn = 0;
        //            return ex.ToString();
        //        }
        //    }
        //}

        #endregion
    }

    public class AccessHelper : IDatabase
    {
        OleDbConnection conn;
        AccessLinkPara LinkPara;
        object obj = new object();

        public AccessHelper(AccessLinkPara para)
        {
            LinkPara = para;
        }
        public bool Connect(out DBRes res)
        {
            res = new DBRes();
            try
            {
                conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + LinkPara.DbPath);
                conn.Open();
                res.bRtn = true;
            }
            catch (Exception ex)
            {
                res.bRtn = false;
                res.msg = ex.Message;
            }
            return res.bRtn;
        }

        public void DisConnect()
        {
            conn.Close();
            conn.Dispose();
        }

        public DataTable ExcuteCmd_DataTable(string cmdText, out DBRes res)
        {
            lock (obj)
            {
                res = new DBRes();
                if (!Connect(out res)) return new DataTable();
                DataTable dt = new DataTable();
                try
                {
                    OleDbCommand cmd = conn.CreateCommand();
                    cmd.CommandText = cmdText;
                    OleDbDataReader dr = cmd.ExecuteReader();
                    cmd.Dispose();
                    DisConnect();
                    res.bRtn = true;
                    if (dr.HasRows)
                    {
                        for (int i = 0; i < dr.FieldCount; i++)
                        {
                            dt.Columns.Add(dr.GetName(i));
                        }
                        while (dr.Read())
                        {
                            DataRow row = dt.NewRow();
                            for (int i = 0; i < dr.FieldCount; i++)
                            {
                                row[i] = dr[i];
                            }
                            dt.Rows.Add(row);
                        }
                    }
                    else { res.msg = "所获取的表为空!"; }

                }
                catch (Exception ex)
                {
                    res.bRtn = false;
                    res.msg = ex.Message;
                }
                return dt;
            }

        }

        public List<T> GetList<T>(string cmdText, out DBRes res)
        {
            lock (obj)
            {
                res = new DBRes();
                DataTable dt = ExcuteCmd_DataTable(cmdText, out res);
                if (!res.bRtn) return new List<T>();
                List<T> list = new List<T>();
                try
                {
                    Type type = typeof(T);
                    foreach (DataRow itemRow in dt.Rows)
                    {
                        T listitem = (T)Activator.CreateInstance(type);
                        Type t = listitem.GetType();
                        object val;
                        foreach (DataColumn itemCol in dt.Columns)
                        {
                            val = itemRow[itemCol];
                            val = val == null ? "" : val.ToString().Trim();
                            val = val == DBNull.Value ? "" : val.ToString().Trim();
                            PropertyInfo info = t.GetProperty(itemCol.ColumnName);
                            if (info == null || info.PropertyType == null)
                            {
                                continue;
                            }
                            string _t = info.PropertyType.FullName;
                            if (_t == typeof(double).ToString())
                            {
                                if (string.IsNullOrWhiteSpace(val.ToString())) val = 0;
                                info.SetValue(listitem, Convert.ChangeType(val, typeof(double)), null);
                            }
                            else if (_t == typeof(int).ToString())
                            {
                                if (string.IsNullOrWhiteSpace(val.ToString())) val = 0;
                                info.SetValue(listitem, Convert.ChangeType(val, typeof(int)), null);
                            }
                            else if (_t == typeof(DateTime).ToString())
                            {
                                info.SetValue(listitem, Convert.ChangeType(val, typeof(DateTime)), null);
                            }
                            else if (_t == typeof(bool).ToString())
                            {
                                info.SetValue(listitem, Convert.ChangeType(val, typeof(bool)), null);
                            }
                            else
                            {
                                info.SetValue(listitem, val.ToString(), null);
                            }
                        }
                        list.Add(listitem);
                    }
                    res.bRtn = true;
                }
                catch (Exception ex)
                {
                    res.bRtn = false;
                    res.msg = ex.Message;
                }
                return list;
            }
        }

        public void ExcuteCmd(string cmdText, out DBRes res)
        {
            lock (obj)
            {
                res = new DBRes();
                if (!Connect(out res)) return;
                try
                {
                    OleDbCommand cmd = new OleDbCommand();
                    cmd.CommandText = cmdText;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    DisConnect();
                    res.bRtn = true;
                }
                catch (Exception ex)
                {
                    res.bRtn = false;
                    res.msg = ex.Message;
                }
            }

        }

        public void ExcuteProc(string cmdText, SqlParameter[] para, out DBRes res)
        {
            res = new DBRes();
        }

        public ObservableCollection<T> GetList2<T>(string commandText, out DBRes res)
        {
            res = new DBRes();
            List<T> li = GetList<T>(commandText, out res);
            return new ObservableCollection<T>(li);
        }

        public void InsertRow<T>(string table, T _type, out DBRes res, bool ignoreID = true)
        {
            try
            {
                res = new DBRes();
                Type t = typeof(T);
                PropertyInfo[] info = t.GetProperties();

                string colNames = "", vals = "";
                foreach (PropertyInfo item in info)
                {
                    if (item.Name == "ID" && ignoreID) continue;
                    colNames += item.Name + ",";
                    object val = item.GetValue(_type);
                    vals += $"'{val}',";
                }
                colNames = colNames.Substring(0, colNames.Length - 1);
                vals = vals.Substring(0, vals.Length - 1);
                string cmd = $"insert into {table}({colNames}) values({vals})";

                ExcuteCmd(cmd, out res);
            }
            catch (Exception ex)
            {
                res.bRtn = false;
                res.msg = ex.Message + "\r\n" + ex.StackTrace;
            }
        }
    }

    public class SQLiteHelper : IDatabase
    {
        //SQLite数据库datetime格式必须为 "2019-01-02T08:05:15",也就是日期与时间中间加个"T",且日期与时间只有1位的必须补0
        //设置某列为自增加的标志列，如ID列，要设置如下两个属性： PRIMARY KEY, AUTOINCREMENT

        SQLiteConnection conn;
        SqlLiteLinkPara LinkPara;
        object obj = new object();

        public SQLiteHelper(SqlLiteLinkPara para)
        {
            LinkPara = para;
        }
        public bool Connect(out DBRes res)
        {
            res = new DBRes();
            try
            {
                conn = new SQLiteConnection($"Data Source={LinkPara.DbPath};Version=3;");
                conn.Open();
                res.bRtn = true;
            }
            catch (Exception ex)
            {
                res.bRtn = false;
                res.msg = ex.Message;
            }
            return res.bRtn;
        }

        public void DisConnect()
        {
            conn.Close();
            conn.Dispose();
        }

        public DataTable ExcuteCmd_DataTable(string cmdText, out DBRes res)
        {
            lock (obj)
            {
                res = new DBRes();
                if (!Connect(out res)) return new DataTable();
                DataTable dt = new DataTable();
                try
                {
                    SQLiteCommand cmd = conn.CreateCommand();
                    cmd.CommandText = cmdText;
                    SQLiteDataReader dr = cmd.ExecuteReader();
                    cmd.Dispose();
                    res.bRtn = true;
                    if (dr.HasRows)
                    {
                        for (int i = 0; i < dr.FieldCount; i++)
                        {
                            dt.Columns.Add(dr.GetName(i));
                        }
                        while (dr.Read())
                        {
                            DataRow row = dt.NewRow();
                            for (int i = 0; i < dr.FieldCount; i++)
                            {
                                var val = dr[i];
                                row[i] = val;
                            }
                            dt.Rows.Add(row);
                        }
                    }
                    else { res.msg = "所获取的表为空!"; }
                    DisConnect();
                }
                catch (Exception ex)
                {
                    res.bRtn = false;
                    res.msg = ex.Message;
                }
                return dt;
            }

        }

        public List<T> GetList<T>(string cmdText, out DBRes res)
        {
            lock (obj)
            {
                res = new DBRes();
                DataTable dt = ExcuteCmd_DataTable(cmdText, out res);
                if (!res.bRtn) return new List<T>();
                List<T> list = new List<T>();
                try
                {
                    Type type = typeof(T);
                    foreach (DataRow itemRow in dt.Rows)
                    {
                        T listitem = (T)Activator.CreateInstance(type);
                        Type t = listitem.GetType();
                        object val;
                        foreach (DataColumn itemCol in dt.Columns)
                        {
                            val = itemRow[itemCol];
                            val = val == null ? "" : val.ToString().Trim();
                            val = val == DBNull.Value ? "" : val.ToString().Trim();
                            PropertyInfo info = t.GetProperty(itemCol.ColumnName);
                            if (info == null || info.PropertyType == null)
                            {
                                continue;
                            }
                            string _t = info.PropertyType.FullName;
                            if (_t == typeof(double).ToString())
                            {
                                if (string.IsNullOrWhiteSpace(val.ToString())) val = 0;
                                info.SetValue(listitem, Convert.ChangeType(val, typeof(double)), null);
                            }
                            else if (_t == typeof(int).ToString())
                            {
                                if (string.IsNullOrWhiteSpace(val.ToString())) val = 0;
                                info.SetValue(listitem, Convert.ChangeType(val, typeof(int)), null);
                            }
                            else if (_t == typeof(DateTime).ToString())
                            {
                                info.SetValue(listitem, Convert.ChangeType(val, typeof(DateTime)), null);
                            }
                            else if (_t == typeof(bool).ToString())
                            {
                                if (string.IsNullOrWhiteSpace(val.ToString())) val = "false";
                                info.SetValue(listitem, Convert.ChangeType(val, typeof(bool)), null);
                            }
                            else
                            {
                                info.SetValue(listitem, val.ToString(), null);
                            }
                        }
                        list.Add(listitem);
                    }
                    res.bRtn = true;
                }
                catch (Exception ex)
                {
                    res.bRtn = false;
                    res.msg = ex.Message;
                }
                return list;
            }
        }

        public ObservableCollection<T> GetList2<T>(string commandText, out DBRes res)
        {
            res = new DBRes();
            List<T> li = GetList<T>(commandText, out res);
            return new ObservableCollection<T>(li);
        }

        public void ExcuteCmd(string cmdText, out DBRes res)
        {
            lock (obj)
            {
                res = new DBRes();
                if (!Connect(out res)) return;
                try
                {
                    SQLiteCommand cmd = new SQLiteCommand();
                    cmd.CommandText = cmdText;
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    DisConnect();
                    res.bRtn = true;
                }
                catch (Exception ex)
                {
                    res.bRtn = false;
                    res.msg = ex.Message;
                }
            }

        }

        public void ExcuteProc(string cmdText, SqlParameter[] para, out DBRes res)
        {
            res = new DBRes();
        }

        public void InsertCloneRow<T>(T tInst,string dstPrj, out DBRes res)
        {
            try
            {
                res = new DBRes();
                Type t = typeof(T);
                PropertyInfo[] info = t.GetProperties();

                string colNames = "", vals = "";
                foreach (PropertyInfo item in info)
                {
                    if (item.Name == "ID") continue;
                    colNames += item.Name + ",";
                    object val = item.GetValue(tInst);
                    if (item.Name == "Project") val = dstPrj;
                    vals += $"'{val}',";
                }
                colNames = colNames.Substring(0, colNames.Length - 1);
                vals = vals.Substring(0, vals.Length - 1);
                string cmd = $"insert into {t.Name}({colNames}) values({vals})";

                ExcuteCmd(cmd, out res);
            }
            catch (Exception ex)
            {
                res.bRtn = false;
                res.msg = ex.Message + "\r\n" + ex.StackTrace;
            }
        }

        public void InsertSpaceRow<T>(out DBRes res)
        {
            try
            {
                res = new DBRes();
                Type t = typeof(T);
                PropertyInfo[] info = t.GetProperties();

                string colNames = "", vals = "";
                foreach (PropertyInfo item in info)
                {
                    if (item.Name == "ID") continue;
                    colNames += item.Name + ",";
                 
                    vals += $"' ',";
                }
                colNames = colNames.Substring(0, colNames.Length - 1);
                vals = vals.Substring(0, vals.Length - 1);
                string cmd = $"insert into {t.Name}({colNames}) values({vals})";

                ExcuteCmd(cmd, out res);
            }
            catch (Exception ex)
            {
                res.bRtn = false;
                res.msg = ex.Message + "\r\n" + ex.StackTrace;
            }
        }

        public void InsertRow<T>(string table, T _type, out DBRes res, bool ignoreID = true)
        {
            try
            {
                res = new DBRes();
                Type t = typeof(T);
                PropertyInfo[] info = t.GetProperties();

                string colNames = "", vals = "";
                foreach (PropertyInfo item in info)
                {
                    if (item.Name == "ID" && ignoreID) continue;
                    colNames += item.Name + ",";
                    object val = item.GetValue(_type);
                    if (val !=null && val.GetType() == typeof(DateTime)) val = ((DateTime)val).ToString("yyyy-MM-ddTHH:mm:ss");
                    vals += $"'{val}',";
                }
                colNames = colNames.Substring(0, colNames.Length - 1);
                vals = vals.Substring(0, vals.Length - 1);
                string cmd = $"insert into {table}({colNames}) values({vals})";

                ExcuteCmd(cmd, out res);
            }
            catch (Exception ex)
            {
                res.bRtn = false;
                res.msg = ex.Message + "\r\n" + ex.StackTrace;
            }
        }

        public void InsertLog(string type,string method,string msg)
        {
            try
            {
                string cmd = $"insert into Log(Type,Method,Msg,DateTime) values ('{type}','{method}','{msg}','{DateTime.Now}')";
                DBRes res;
                ExcuteCmd(cmd, out res);
            }
            catch (Exception)
            {
            }
        }
        public void InsertData(string prj, string sn1d, string sn2d,double area1,double area2,string result)
        {
            try
            {
                string dt = DateTime.Now.ToString("yyyy-MM-ddTHH:mm:ss");
                string cmd = $"insert into Data(Project,SN1d,SN2d,Area1,Area2,Result,Time) values ('{prj}','{sn1d}','{sn2d}','{area1}','{area2}','{result}','{dt}')";
                DBRes res;
                ExcuteCmd(cmd, out res);
            }
            catch (Exception)
            {
            }
        }
        public void InsertAlarm(string type, string method, string msg)
        {
            try
            {
                string dt = DateTime.Now.ToString("yyyy-MM-ddTHH:mm:ss");
                string cmd = $"insert into Alarm(Type,Method,Msg,Time) values ('{type}','{method}','{msg}','{dt}')";
                DBRes res;
                ExcuteCmd(cmd, out res);
            }
            catch (Exception)
            {
            }
        }
        public void InitTable<T>(string table, string item, out DBRes res)
        {
            lock (obj)
            {
                res = new DBRes();
                if (!Connect(out res)) return;
                try
                {
                    List<ParaTable> li = GetList<ParaTable>($"select * from {table}", out res);
                    Type t = typeof(T);
                    PropertyInfo[] info = t.GetProperties();

                    Type tParaTable = typeof(ParaTable);
                    PropertyInfo iPara = tParaTable.GetProperty("Para");
                    PropertyInfo iItem = tParaTable.GetProperty("Item");
                    for (int i = 0; i < info.Count(); i++)
                    {
                        if (info[i] != null)
                        {
                            string Para;
                            string Val;
                            Para = info[i].Name;

                            object attri = info[i].GetCustomAttribute(typeof(DescriptionAttribute), true);
                            if (attri == null || attri.ToString().Length <= 0 || string.IsNullOrWhiteSpace(((DescriptionAttribute)attri).Description)) Val = "0";
                            else Val = ((DescriptionAttribute)attri).Description;

                            List<ParaTable> liTmp;
                            if (string.IsNullOrWhiteSpace(item))
                                liTmp = li.Where((p) => { return iPara.GetValue(p).ToString() == Para; }).ToList<ParaTable>();
                            else liTmp = li.Where((p) => { return (iPara.GetValue(p).ToString() == Para) && (iItem.GetValue(p).ToString() == item); }).ToList<ParaTable>();

                            if (liTmp == null || liTmp.Count <= 0)
                            {
                                if (string.IsNullOrWhiteSpace(item))
                                    ExcuteCmd($"insert into {table}(Para,Val) values('{Para}','{Val}')", out res);
                                else
                                    ExcuteCmd($"insert into {table}(Para,Val,Item) values('{Para}','{Val}','{item}')", out res);
                            }
                        }
                    }
                    DisConnect();
                    res.bRtn = true;
                }
                catch (Exception ex)
                {
                    res.bRtn = false;
                    res.msg = ex.Message;
                }
            }
        }

        public void ReadPara<T>(string tbName, T t, string prj = null)
        {
            try
            {
                DBRes res;
                Type type = typeof(T);
                List<T> li;
                if (string.IsNullOrWhiteSpace(prj)) li = GetList<T>($"select * from {tbName} limit 0,1", out res);
                else li = GetList<T>($"select * from {tbName} where curPrj='{prj}' limit 0,1", out res);
                if (!res.bRtn) { WriteAlarm(AlmType.Soft, $"[ReadPara]读取参数出错:{res.msg}"); return; }
                if (li == null || li.Count <= 0) return;
                PropertyInfo[] info = type.GetProperties();
                foreach (PropertyInfo item in info)
                {
                    object str = item.GetValue(li[0]);
                    item.SetValue(t, str);
                }
            }
            catch (Exception ex)
            {
                WriteAlarm(AlmType.Soft, $"[ReadPara]读取参数出错:{ex.Message}");
            }
        }

        public List<T> ReadPara<T>(string prj, out DBRes res)
        {
            lock (obj)
            {
                res = new DBRes();
                DataTable dt = ExcuteCmd_DataTable($"select * from {typeof(T).Name} where Project = '{prj}'", out res);
                if (!res.bRtn) return new List<T>();
                List<T> list = new List<T>();
                try
                {
                    Type type = typeof(T);
                    foreach (DataRow itemRow in dt.Rows)
                    {
                        T listitem = (T)Activator.CreateInstance(type);
                        Type t = listitem.GetType();
                        object val;
                        foreach (DataColumn itemCol in dt.Columns)
                        {
                            val = itemRow[itemCol];
                            val = val == null ? "" : val.ToString().Trim();
                            val = val == DBNull.Value ? "" : val.ToString().Trim();
                            PropertyInfo info = t.GetProperty(itemCol.ColumnName);
                            if (info == null || info.PropertyType == null)
                            {
                                continue;
                            }
                            string _t = info.PropertyType.FullName;
                            if (_t == typeof(double).ToString())
                            {
                                if (string.IsNullOrWhiteSpace(val.ToString())) val = 0;
                                info.SetValue(listitem, Convert.ChangeType(val, typeof(double)), null);
                            }
                            else if (_t == typeof(int).ToString())
                            {
                                if (string.IsNullOrWhiteSpace(val.ToString())) val = 0;
                                info.SetValue(listitem, Convert.ChangeType(val, typeof(int)), null);
                            }
                            else if (_t == typeof(DateTime).ToString())
                            {
                                info.SetValue(listitem, Convert.ChangeType(val, typeof(DateTime)), null);
                            }
                            else if (_t == typeof(bool).ToString())
                            {
                                bool b = false;
                                if (val != null && (val.ToString().Trim().ToUpper() == "TRUE")||(val.ToString().Trim().ToUpper()=="1")) b = true;
                                info.SetValue(listitem, b, null);
                            }
                            else
                            {
                                info.SetValue(listitem, val.ToString(), null);
                            }
                        }
                        list.Add(listitem);
                    }
                    res.bRtn = true;
                }
                catch (Exception ex)
                {
                    res.bRtn = false;
                    res.msg = ex.Message;
                }
                return list;
            }
        }

        public void WritePara<T>(string tbName, T t, string prj = null)
        {
            try
            {
                DBRes res;
                Type type = typeof(T);
                string cmd = $"update {tbName} set ";
                PropertyInfo[] info = type.GetProperties();
                foreach (PropertyInfo item in info)
                {
                    object val = item.GetValue(t);
                    cmd += $"{item.Name}='{val}',";
                }
                if (string.IsNullOrWhiteSpace(prj))
                    cmd = cmd.Remove(cmd.Length - 1, 1);
                else cmd = cmd.Remove(cmd.Length - 1, 1) + $" where Prj='{prj}'";
                ExcuteCmd(cmd, out res);
                if (!res.bRtn) { WriteAlarm(AlmType.Soft, $"[WritePara]更新参数出错:{res.msg}"); }
            }
            catch (Exception ex)
            {
                WriteAlarm(AlmType.Soft, $"[WritePara]更新参数出错:{ex.Message}");
            }

        }

        public void CreateTable<T>(string tbName, bool bPrj = false)
        {
            DBRes res;
            DataTable dt = ExcuteCmd_DataTable($"select * from sqlite_master where type = 'table' and name = '{tbName}'", out res);
            bool isNew = true;//是全新创建的表，还是仅仅添加一些字段
            string cmd = $"create table {tbName} (ID INTEGER PRIMARY KEY AUTOINCREMENT";
            if (dt != null && dt.Columns.Count > 0)
            {
                isNew = false;
                dt = ExcuteCmd_DataTable($"select top 1 from {tbName}", out res);
            }

            Type t = typeof(T);
            PropertyInfo[] info = t.GetProperties();
            foreach (PropertyInfo pInfo in info)
            {
                string name = pInfo.Name;
                if (name == "ID") continue;
                if (!isNew)
                {
                    if (dt.Columns.Contains(name)) continue;
                    cmd = $"alter table {tbName} add {name} ";
                }
                else
                {
                    cmd += "," + name + " ";
                }
                Type pType = pInfo.PropertyType;
                if (pType.Equals(typeof(int)))
                {
                    cmd += "INTEGER";
                }
                else if (pType.Equals(typeof(double)))
                {
                    cmd += "DOUBLE";
                }
                else if (pType.Equals(typeof(DateTime)))
                {
                    cmd += "DATETIME";
                }
                else if (pType.Equals(typeof(bool)))
                {
                    cmd += "BOOLEAN";
                }
                else
                {
                    cmd += "STRING";
                }
                if (!isNew) ExcuteCmd(cmd, out res);
            }
            if (!isNew) return;
            //判断是否有字段“项目”,如果有则加入，表示此表的参数根据项目不同而不同
            if (bPrj) cmd += ",Prj STRING";
            cmd += ")";
            ExcuteCmd(cmd, out res);
        }

        public void CreateTable<T>(string tbName="")
        {
            Type t = typeof(T);
            PropertyInfo[] info = t.GetProperties();
            if (string.IsNullOrWhiteSpace(tbName)) tbName = t.Name;
            DBRes res;
            DataTable dt = ExcuteCmd_DataTable($"select * from sqlite_master where type = 'table' and name = '{tbName}'", out res);
            bool isNew = true;//是全新创建的表，还是仅仅添加一些字段
            string cmd = $"create table {tbName} (ID INTEGER PRIMARY KEY AUTOINCREMENT";
            if (dt != null && dt.Columns.Count > 0)
            {
                isNew = false;
                dt = ExcuteCmd_DataTable($"select top 1 from {tbName}", out res);
            }

            foreach (PropertyInfo pInfo in info)
            {
                string name = pInfo.Name;
                if (name == "ID") continue;
                if (!isNew)
                {
                    if (dt.Columns.Contains(name)) continue;
                    cmd = $"alter table {tbName} add {name} ";
                }
                else
                {
                    cmd += "," + name + " ";
                }
                Type pType = pInfo.PropertyType;
                if (pType.Equals(typeof(int)))
                {
                    cmd += "INTEGER";
                }
                else if (pType.Equals(typeof(double)))
                {
                    cmd += "DOUBLE";
                }
                else if (pType.Equals(typeof(DateTime)))
                {
                    cmd += "DATETIME";
                }
                else if (pType.Equals(typeof(bool)))
                {
                    cmd += "BOOLEAN";
                }
                else
                {
                    cmd += "STRING";
                }
                if (!isNew) ExcuteCmd(cmd, out res);
            }
            if (!isNew) return;
            cmd += ")";
            ExcuteCmd(cmd, out res);
        }

        //public void UpdatePara<T>(string item, T liVal, out DBRes res)
        //{
        //    lock (obj)
        //    {
        //        res = new DBRes();
        //        try
        //        {
        //            Type t = typeof(T);
        //            PropertyInfo[] info = t.GetProperties();
        //            for (int i = 0; i < info.Count(); i++)
        //            {
        //                if (info[i] != null)
        //                {
        //                    string Para;
        //                    string Val;

        //                    Para = info[i].Name;
        //                    Val = info[i].GetValue(liVal).ToString();
        //                    if (string.IsNullOrWhiteSpace(Para) || string.IsNullOrWhiteSpace(Val)) continue;

        //                    if (string.IsNullOrWhiteSpace(item))
        //                        ExcuteCmd($"update ItemPara set Val='{Val}' where Para='{Para}'", out res);
        //                    else
        //                        ExcuteCmd($"update ItemPara set Val='{Val}' where Para='{Para}' and Item='{item}'", out res);
        //                }
        //            }

        //            res.bRtn = true;
        //        }
        //        catch (Exception ex)
        //        {
        //            res.bRtn = false;
        //            res.msg = ex.Message;
        //        }
        //    }
        //}

        public void UpdatePara<T>(string prj,T para, out DBRes res)
        {
            lock (obj)
            {
                res = new DBRes();
                try
                {
                   
                    Type t = typeof(T);
                    PropertyInfo[] info = t.GetProperties();
                    string cmd = $"update {t.Name} set ";
                    for (int i = 0; i < info.Count(); i++)
                    {
                        if (info[i] != null)
                        {
                            if (info[i].Name == "ID") continue;
                            string Para;
                            string Val;

                            Para = info[i].Name;
                            Val = info[i].GetValue(para).ToString();
                            if (string.IsNullOrWhiteSpace(Para) || string.IsNullOrWhiteSpace(Val)) continue;

                            cmd += $"{Para}='{Val}',";
                        }
                    }
                    cmd = cmd.Remove(cmd.Length - 1, 1) + $" where PrjName='{prj}'";
                    ExcuteCmd(cmd, out res);
                    res.bRtn = true;
                }
                catch (Exception ex)
                {
                    res.bRtn = false;
                    res.msg = ex.Message;
                }
            }
        }
        public void UpdatePara<T>(int id, T para, out DBRes res)
        {
            lock (obj)
            {
                res = new DBRes();
                try
                {

                    Type t = typeof(T);
                    PropertyInfo[] info = t.GetProperties();
                    string cmd = $"update {t.Name} set ";
                    for (int i = 0; i < info.Count(); i++)
                    {
                        if (info[i] != null)
                        {
                            if (info[i].Name == "ID") continue;
                            string Para;
                            string Val;

                            Para = info[i].Name;
                            Val = info[i].GetValue(para).ToString();
                            if (string.IsNullOrWhiteSpace(Para) || string.IsNullOrWhiteSpace(Val)) continue;

                            cmd += $"{Para}='{Val}',";
                        }
                    }
                    cmd = cmd.Remove(cmd.Length - 1, 1) + $" where ID='{id}'";
                    ExcuteCmd(cmd, out res);
                    res.bRtn = true;
                }
                catch (Exception ex)
                {
                    res.bRtn = false;
                    res.msg = ex.Message;
                }
            }
        }
        public void UpdatePara<T>(T para, out DBRes res)
        {
            lock (obj)
            {
                res = new DBRes();
                try
                {
                    string id="";
                    Type t = typeof(T);
                    PropertyInfo[] info = t.GetProperties();
                    string cmd = $"update {t.Name} set ";
                    for (int i = 0; i < info.Count(); i++)
                    {
                        if (info[i] != null)
                        {
                         
                            string Para;
                            string Val;

                            Para = info[i].Name;
                            Val = info[i].GetValue(para).ToString().Trim();
                            if (string.IsNullOrWhiteSpace(Para) || string.IsNullOrWhiteSpace(Val)) continue;

                            if (info[i].Name == "ID") { id = Val; continue; }
                            cmd += $"{Para}='{Val}',";
                        }
                    }
                    cmd = cmd.Remove(cmd.Length - 1, 1);
                    ExcuteCmd(cmd + $" where ID='{id}'", out res);
                    res.bRtn = true;
                }
                catch (Exception ex)
                {
                    res.bRtn = false;
                    res.msg = ex.Message;
                }
            }
        }
        public void WriteAlarm(AlmType ty, string msg)
        {
            lock (obj)
            {
                DBRes res;
                res = new DBRes();
                if (!Connect(out res)) return;
                string type = "";
                switch (ty)
                {
                    case AlmType.Soft:
                        type = "软件";
                        break;
                    case AlmType.Cylinder:
                        type = "气缸";
                        break;
                    case AlmType.Sensor:
                        type = "感应器";
                        break;
                    case AlmType.Axis:
                        type = "轴系";
                        break;
                    case AlmType.Other:
                        type = "其他";
                        break;
                    default:
                        type = "其他";
                        break;
                }
                try
                {
                    SQLiteCommand cmd = new SQLiteCommand();
                    cmd.CommandText = $"insert into Alarm(Type,Site,Time,Msg) values('{type}','null','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}','{msg}')";
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    DisConnect();
                    res.bRtn = true;
                    WriteLog(msg, true);
                }
                catch (Exception ex)
                {
                    res.bRtn = false;
                    res.msg = ex.Message;
                }
            }
        }

        public void WriteLog(string msg, bool isErr = false)
        {
            lock (obj)
            {
                DBRes res;
                res = new DBRes();
                if (!Connect(out res)) return;
                string type = "Normal";
                if (isErr) type = "Error";
                try
                {
                    SQLiteCommand cmd = new SQLiteCommand();
                    cmd.CommandText = $"insert into Log(Type,Time,Msg) values('{type}','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}','{msg}')";
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = conn;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    DisConnect();
                    res.bRtn = true;
                }
                catch (Exception ex)
                {
                    res.bRtn = false;
                    res.msg = ex.Message;
                }
            }
        }
    }


    class ParaTable
    {
        public int ID { get; set; }
        public string Para { get; set; }
        public string Val { get; set; }
        public string Item { get; set; }

    }
}
